from datetime import datetime
from typing import List, Optional, Dict, Any
from sqlalchemy import select, update, delete, desc, and_, or_, func
from sqlalchemy.ext.asyncio import AsyncSession
from module_speech.entity.do.speech_record_do import SpeechRecordDO
from module_speech.entity.vo.speech_vo import SpeechQueryModel


class SpeechRecordDao:
    """
    语音识别记录数据访问层
    """

    @classmethod
    async def get_speech_record_detail_by_id(cls, db: AsyncSession, speech_id: int) -> Optional[SpeechRecordDO]:
        """
        根据语音记录ID获取详细信息

        :param db: 数据库会话
        :param speech_id: 语音记录ID
        :return: 语音记录对象或None
        """
        query = select(SpeechRecordDO).where(
            and_(SpeechRecordDO.speech_id == speech_id, SpeechRecordDO.del_flag == 0)
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_speech_record_list(cls, db: AsyncSession, query_object: SpeechQueryModel) -> List[SpeechRecordDO]:
        """
        获取语音识别记录列表

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 语音记录列表
        """
        query = select(SpeechRecordDO).where(SpeechRecordDO.del_flag == 0)
        
        # 添加查询条件
        if query_object.user_id:
            query = query.where(SpeechRecordDO.user_id == query_object.user_id)
        if query_object.client_user_id:
            query = query.where(SpeechRecordDO.client_user_id == query_object.client_user_id)
        if query_object.user_type:
            query = query.where(SpeechRecordDO.user_type == query_object.user_type)
        if query_object.session_id:
            query = query.where(SpeechRecordDO.session_id == query_object.session_id)
        if query_object.api_request_id:
            query = query.where(SpeechRecordDO.api_request_id == query_object.api_request_id)
        if query_object.status is not None:
            query = query.where(SpeechRecordDO.status == query_object.status)
        if query_object.language:
            query = query.where(SpeechRecordDO.language == query_object.language)
        if query_object.model_name:
            query = query.where(SpeechRecordDO.model_name == query_object.model_name)
        if query_object.callback_status is not None:
            query = query.where(SpeechRecordDO.callback_status == query_object.callback_status)
        if query_object.create_time_start:
            query = query.where(SpeechRecordDO.create_time >= query_object.create_time_start)
        if query_object.create_time_end:
            query = query.where(SpeechRecordDO.create_time <= query_object.create_time_end)
        
        # 按创建时间倒序排列
        query = query.order_by(desc(SpeechRecordDO.create_time))
        
        # 分页
        offset = (query_object.page_num - 1) * query_object.page_size
        query = query.offset(offset).limit(query_object.page_size)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def count_speech_records(cls, db: AsyncSession, query_object: SpeechQueryModel) -> int:
        """
        统计语音识别记录总数

        :param db: 数据库会话
        :param query_object: 查询参数对象
        :return: 记录总数
        """
        query = select(func.count(SpeechRecordDO.speech_id)).where(SpeechRecordDO.del_flag == 0)
        
        # 添加查询条件
        if query_object.user_id:
            query = query.where(SpeechRecordDO.user_id == query_object.user_id)
        if query_object.client_user_id:
            query = query.where(SpeechRecordDO.client_user_id == query_object.client_user_id)
        if query_object.user_type:
            query = query.where(SpeechRecordDO.user_type == query_object.user_type)
        if query_object.session_id:
            query = query.where(SpeechRecordDO.session_id == query_object.session_id)
        if query_object.api_request_id:
            query = query.where(SpeechRecordDO.api_request_id == query_object.api_request_id)
        if query_object.status is not None:
            query = query.where(SpeechRecordDO.status == query_object.status)
        if query_object.language:
            query = query.where(SpeechRecordDO.language == query_object.language)
        if query_object.model_name:
            query = query.where(SpeechRecordDO.model_name == query_object.model_name)
        if query_object.callback_status is not None:
            query = query.where(SpeechRecordDO.callback_status == query_object.callback_status)
        if query_object.create_time_start:
            query = query.where(SpeechRecordDO.create_time >= query_object.create_time_start)
        if query_object.create_time_end:
            query = query.where(SpeechRecordDO.create_time <= query_object.create_time_end)
        
        result = await db.execute(query)
        return result.scalar()

    @classmethod
    async def add_speech_record_dao(cls, db: AsyncSession, speech_record: SpeechRecordDO) -> SpeechRecordDO:
        """
        新增语音识别记录

        :param db: 数据库会话
        :param speech_record: 语音记录对象
        :return: 新增的语音记录对象
        """
        db.add(speech_record)
        await db.flush()
        await db.refresh(speech_record)
        return speech_record

    @classmethod
    async def edit_speech_record_dao(cls, db: AsyncSession, speech_record: SpeechRecordDO) -> SpeechRecordDO:
        """
        编辑语音识别记录

        :param db: 数据库会话
        :param speech_record: 语音记录对象
        :return: 更新后的语音记录对象
        """
        speech_record.update_time = datetime.now()
        await db.flush()
        await db.refresh(speech_record)
        return speech_record

    @classmethod
    async def update_speech_status(cls, db: AsyncSession, speech_id: int, status: int, 
                                   recognize_text: str = None, confidence: float = None, 
                                   error_message: str = None, callback_status: int = None,
                                   recognition_details: Dict[str, Any] = None) -> bool:
        """
        更新语音记录状态

        :param db: 数据库会话
        :param speech_id: 语音记录ID
        :param status: 状态：0-处理中, 1-成功, 2-失败
        :param recognize_text: 识别文本
        :param confidence: 置信度
        :param error_message: 错误信息
        :param callback_status: 回调状态
        :param recognition_details: 详细识别结果
        :return: 更新是否成功
        """
        update_data = {
            'status': status,
            'update_time': datetime.now()
        }
        
        if recognize_text:
            update_data['recognize_text'] = recognize_text
        if confidence is not None:
            update_data['confidence'] = confidence
        if error_message:
            update_data['error_message'] = error_message
        if callback_status is not None:
            update_data['callback_status'] = callback_status
        if recognition_details:
            update_data['recognition_details'] = recognition_details
        
        query = update(SpeechRecordDO).where(
            and_(SpeechRecordDO.speech_id == speech_id, SpeechRecordDO.del_flag == 0)
        ).values(**update_data)
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def delete_speech_record_dao(cls, db: AsyncSession, speech_ids: List[int]) -> bool:
        """
        删除语音识别记录（软删除）

        :param db: 数据库会话
        :param speech_ids: 语音记录ID列表
        :return: 删除是否成功
        """
        query = update(SpeechRecordDO).where(
            and_(SpeechRecordDO.speech_id.in_(speech_ids), SpeechRecordDO.del_flag == 0)
        ).values(del_flag=1, update_time=datetime.now())
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def get_records_by_session(cls, db: AsyncSession, session_id: str) -> List[SpeechRecordDO]:
        """
        根据会话ID获取语音记录列表

        :param db: 数据库会话
        :param session_id: 会话ID
        :return: 语音记录列表
        """
        query = select(SpeechRecordDO).where(
            and_(SpeechRecordDO.session_id == session_id, SpeechRecordDO.del_flag == 0)
        ).order_by(SpeechRecordDO.create_time)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def get_speech_records_by_client_user(cls, db: AsyncSession, client_user_id: int,
                                                 limit: int = 10, offset: int = 0) -> List[SpeechRecordDO]:
        """
        根据客户端用户ID获取语音记录列表

        :param db: 数据库会话
        :param client_user_id: 客户端用户ID
        :param limit: 限制数量
        :param offset: 偏移量
        :return: 语音记录列表
        """
        query = select(SpeechRecordDO).where(
            and_(SpeechRecordDO.client_user_id == client_user_id, SpeechRecordDO.del_flag == 0)
        ).order_by(desc(SpeechRecordDO.create_time)).offset(offset).limit(limit)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def get_speech_records_by_api_request_id(cls, db: AsyncSession, api_request_id: str) -> Optional[SpeechRecordDO]:
        """
        根据API请求ID获取语音记录

        :param db: 数据库会话
        :param api_request_id: API请求ID
        :return: 语音记录对象或None
        """
        query = select(SpeechRecordDO).where(
            and_(SpeechRecordDO.api_request_id == api_request_id, SpeechRecordDO.del_flag == 0)
        )
        result = await db.execute(query)
        return result.scalars().first()

    @classmethod
    async def get_speech_records_by_api_request_ids(cls, db: AsyncSession, api_request_ids: List[str]) -> List[SpeechRecordDO]:
        """
        根据API请求ID列表批量获取语音记录

        :param db: 数据库会话
        :param api_request_ids: API请求ID列表
        :return: 语音记录列表
        """
        query = select(SpeechRecordDO).where(
            and_(SpeechRecordDO.api_request_id.in_(api_request_ids), SpeechRecordDO.del_flag == 0)
        ).order_by(desc(SpeechRecordDO.create_time))
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def get_pending_callback_records(cls, db: AsyncSession, limit: int = 100) -> List[SpeechRecordDO]:
        """
        获取待回调的语音记录

        :param db: 数据库会话
        :param limit: 限制数量
        :return: 待回调记录列表
        """
        query = select(SpeechRecordDO).where(
            and_(
                SpeechRecordDO.del_flag == 0,
                SpeechRecordDO.callback_url.isnot(None),
                SpeechRecordDO.callback_status == 0,  # 未回调
                SpeechRecordDO.status.in_([1, 2])     # 已完成或失败
            )
        ).order_by(SpeechRecordDO.create_time).limit(limit)
        
        result = await db.execute(query)
        return result.scalars().all()

    @classmethod
    async def update_callback_status(cls, db: AsyncSession, speech_id: int, callback_status: int) -> bool:
        """
        更新回调状态

        :param db: 数据库会话
        :param speech_id: 语音记录ID
        :param callback_status: 回调状态：0-未回调, 1-回调成功, 2-回调失败
        :return: 更新是否成功
        """
        query = update(SpeechRecordDO).where(
            and_(SpeechRecordDO.speech_id == speech_id, SpeechRecordDO.del_flag == 0)
        ).values(callback_status=callback_status, update_time=datetime.now())
        
        result = await db.execute(query)
        return result.rowcount > 0

    @classmethod
    async def get_user_statistics(cls, db: AsyncSession, user_id: int = None, 
                                  client_user_id: int = None, 
                                  start_date: datetime = None, 
                                  end_date: datetime = None) -> Dict[str, Any]:
        """
        获取用户统计信息

        :param db: 数据库会话
        :param user_id: 系统用户ID
        :param client_user_id: 客户端用户ID
        :param start_date: 开始日期
        :param end_date: 结束日期
        :return: 统计信息
        """
        base_query = select(SpeechRecordDO).where(SpeechRecordDO.del_flag == 0)
        
        # 用户条件
        if user_id:
            base_query = base_query.where(SpeechRecordDO.user_id == user_id)
        if client_user_id:
            base_query = base_query.where(SpeechRecordDO.client_user_id == client_user_id)
        
        # 时间条件
        if start_date:
            base_query = base_query.where(SpeechRecordDO.create_time >= start_date)
        if end_date:
            base_query = base_query.where(SpeechRecordDO.create_time <= end_date)
        
        # 统计查询
        total_query = select(func.count(SpeechRecordDO.speech_id)).select_from(base_query.subquery())
        success_query = base_query.where(SpeechRecordDO.status == 1)
        success_count_query = select(func.count(SpeechRecordDO.speech_id)).select_from(success_query.subquery())
        
        duration_query = select(func.sum(SpeechRecordDO.duration)).select_from(success_query.subquery())
        avg_confidence_query = select(func.avg(SpeechRecordDO.confidence)).select_from(success_query.subquery())
        
        # 执行查询
        total_result = await db.execute(total_query)
        success_result = await db.execute(success_count_query)
        duration_result = await db.execute(duration_query)
        confidence_result = await db.execute(avg_confidence_query)
        
        total_count = total_result.scalar() or 0
        success_count = success_result.scalar() or 0
        total_duration = float(duration_result.scalar() or 0)
        avg_confidence = float(confidence_result.scalar() or 0)
        
        return {
            'total_recognitions': total_count,
            'successful_recognitions': success_count,
            'failed_recognitions': total_count - success_count,
            'success_rate': (success_count / total_count * 100) if total_count > 0 else 0,
            'total_duration': total_duration,
            'avg_confidence': avg_confidence
        } 